﻿using MiniExcelLibs;
using Synergy.dbmodel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Synergy.特种报表
{
    public partial class Form订单报工数 : Form
    {
        public Form订单报工数()
        {
            InitializeComponent();
        }

        private void Form订单报工数_Load(object sender, EventArgs e)
        {
            comboBox1.SelectedIndex = 0;
            string sql = $@"select '请选择'  Description
        union all
        select Description from sfc_operation ";

            DataTable dt = SqlSugarHelp.scope.Ado.GetDataTable(sql);
            comboBox工序.DataSource = dt;
            comboBox工序.DisplayMember = "Description";
            comboBox工序.ValueMember = "Description";
            comboBox工序.SelectedIndex = 0;
        }

        private void button刷新_Click(object sender, EventArgs e)
        {
            if (comboBox1.Text == "汇总")
            {
                汇总查询();
            }
            else
            {
                明细查询();
            }
           
        }

        //private void 汇总查询()
        //{
        //    string sql = $@"select ROW_NUMBER() OVER(ORDER BY Z_派工.工序编号 ASC) AS 序号,
        //    Z_派工.工序编号 工序代码,Z_派工.工序名称,CAST( sum(Z_派工.订单数量)  as int)  生产订单总数量,
        //    CAST( isnull(sum(z_baogong.报工数量),0) as int)  报工数量,CAST( sum(Z_派工.订单数量)-isnull(sum(z_baogong.报工数量),0) as int)  未报工数量
        //    from Z_派工 left join z_baogong on z_baogong.生产订单号 = Z_派工.订单号
        //    and z_baogong.生产订单行号 = Z_派工.订单行号  and z_baogong.加工顺序 = Z_派工.加工顺序 where 1=1";

        //    if (this.textBox1.Text != "")
        //    {
        //        sql += " and Z_派工.产品编号 like '%" + this.textBox1.Text + "%'";
        //    }
        //    if (this.comboBox工序.Text != "请选择")
        //    {
        //        sql += " and Z_派工.工序名称 = '" + this.comboBox工序.Text + "'";
        //    }
        //    if (this.textBox3.Text != "")
        //    {
        //        sql += " and Z_派工.订单号 like '%" + this.textBox3.Text + "%'";
        //    }

        //    if (this.dateTimePicker_ksrq.Checked)
        //    {
        //        sql += " and Z_派工.派工日期>='" + this.dateTimePicker_ksrq.Value.ToString("yyyy-MM-dd") + "'";
        //    }
        //    if (this.dateTimePicker_jsrq.Checked)
        //    {
        //        sql += " and Z_派工.派工日期<'" + this.dateTimePicker_jsrq.Value.AddDays(1).ToString("yyyy-MM-dd") + "'";
        //    }

        //    sql += @"group by  Z_派工.工序编号,Z_派工.工序名称
        //             order by Z_派工.工序编号";

        //    DataTable dt = SqlSugarHelp.scope.Ado.GetDataTable(sql);
        //    //在dt最后一行添加合计行
        //    DataRow dr = dt.NewRow();
        //    //dr["序号"] = "合计";
        //    dr["生产订单总数量"] = dt.Compute("sum(生产订单总数量)", "");
        //    dr["报工数量"] = dt.Compute("sum(报工数量)", "");
        //    dr["未报工数量"] = dt.Compute("sum(未报工数量)", "");
        //    dt.Rows.Add(dr);

        //    dataGridView1.Columns.Clear();
        //    dataGridView1.DataSource = dt;
        //    dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

        //}

        private void 汇总查询()
        {
            string sql = $@"select convert(varchar(100),ROW_NUMBER() OVER(ORDER BY Z_派工.工序编号 ASC)) AS 序号,
                        Z_派工.工序编号 工序代码,Z_派工.工序名称,CAST( sum(View_生产订单明细含已关闭.数量)  as int)  生产订单总数量,
                        CAST( isnull(sum(z_baogong.报工数量),0) as int)  报工数量,CAST( sum(View_生产订单明细含已关闭.数量)-isnull(sum(z_baogong.报工数量),0) as int)  未报工数量
                        from Z_派工 join View_生产订单明细含已关闭 on View_生产订单明细含已关闭.生产订单号 = Z_派工.订单号 and View_生产订单明细含已关闭.行号 = Z_派工.订单行号 
                        --left join Z_派工 on View_生产订单明细含已关闭.MoId = Z_派工.生产订单号ID and View_生产订单明细含已关闭.订单行Id = Z_派工.生产订单行ID
                        left join z_baogong on z_baogong.生产订单号 = Z_派工.订单号
                        and z_baogong.生产订单行号 = Z_派工.订单行号  and z_baogong.加工顺序 = Z_派工.加工顺序 
                        where 1=1";

            if (this.textBox1.Text != "")
            {
                sql += " and Z_派工.产品编号  like '%" + this.textBox1.Text + "%'";
            }
            if (this.comboBox工序.Text != "请选择")
            {
                sql += " and Z_派工.工序名称 = '" + this.comboBox工序.Text + "'";
            }
            if (this.textBox3.Text != "")
            {
                sql += " and Z_派工.订单号 like '%" + this.textBox3.Text + "%'";
            }

            if (this.dateTimePicker_ksrq.Checked)
            {
                sql += " and z_baogong.报工时间 >='" + this.dateTimePicker_ksrq.Value.ToString("yyyy-MM-dd") + "'";
            }
            if (this.dateTimePicker_jsrq.Checked)
            {
                sql += " and z_baogong.报工时间 <'" + this.dateTimePicker_jsrq.Value.AddDays(1).ToString("yyyy-MM-dd") + "'";
            }

            sql += @"group by  Z_派工.工序编号,Z_派工.工序名称
                     order by Z_派工.工序编号";

            DataTable dt = SqlSugarHelp.scope.Ado.GetDataTable(sql);

            //在dt最后一行添加合计行
            DataRow dr = dt.NewRow();
            dr["序号"] = "汇总";
            dr["生产订单总数量"] = dt.Compute("sum(生产订单总数量)", "");
            dr["报工数量"] = dt.Compute("sum(报工数量)", "");
            dr["未报工数量"] = dt.Compute("sum(未报工数量)", "");
            dt.Rows.Add(dr);

            dataGridView1.Columns.Clear();
            dataGridView1.DataSource = dt;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

        }

        private void 明细查询()
        {
            string sql = @"select convert(varchar(100),ROW_NUMBER() OVER(ORDER BY Z_派工.订单号 ASC)) AS 序号,
                Z_派工.订单号 生产订单号,Z_派工.订单行号 生产订单行号,Z_派工.产品编号 物料编号,Z_派工.产品名称 物料名称,View_生产订单明细含已关闭.物料规格 物料规格,
                Z_派工.加工顺序,Z_派工.工序名称 工序名称,CAST( View_生产订单明细含已关闭.数量 as int)  生产订单数量,
                CAST( isnull(sum(z_baogong.报工数量),0)as int) 报工数量,Z_baogong.报工时间,
                CAST( sum(View_生产订单明细含已关闭.数量)- isnull(sum(z_baogong.报工数量),0)as int)  未报工数量
                from Z_派工
                join View_生产订单明细含已关闭 on View_生产订单明细含已关闭.生产订单号 = Z_派工.订单号 and View_生产订单明细含已关闭.行号 = Z_派工.订单行号 
                left join z_baogong on z_baogong.生产订单号 = Z_派工.订单号
                and z_baogong.生产订单行号 = Z_派工.订单行号  and z_baogong.加工顺序 = Z_派工.加工顺序 where 1=1";

            if (this.textBox1.Text != "")
            {
                sql += " and Z_派工.产品编号 like '%" + this.textBox1.Text + "%'";
            }
            if (this.comboBox工序.Text != "请选择")
            {
                sql += " and Z_派工.工序名称 = '" + this.comboBox工序.Text + "'";
            }
            if (this.textBox3.Text != "")
            {
                sql += " and Z_派工.订单号 like '%" + this.textBox3.Text + "%'";
            }

            if (this.dateTimePicker_ksrq.Checked)
            {
                sql += " and z_baogong.报工时间>='" + this.dateTimePicker_ksrq.Value.ToString("yyyy-MM-dd") + "'";
            }
            if (this.dateTimePicker_jsrq.Checked)
            {
                sql += " and z_baogong.报工时间<'" + this.dateTimePicker_jsrq.Value.AddDays(1).ToString("yyyy-MM-dd") + "'";
            }

            sql += @"group by  Z_派工.订单号,Z_派工.订单行号,Z_派工.产品编号,Z_派工.产品名称,View_生产订单明细含已关闭.物料规格,View_生产订单明细含已关闭.数量,
                    Z_派工.工序名称,Z_派工.加工顺序,Z_baogong.报工时间
                    order by Z_派工.订单号,Z_派工.订单行号,Z_派工.加工顺序,Z_baogong.报工时间";

            DataTable dt = SqlSugarHelp.scope.Ado.GetDataTable(sql);
            //取出所有的订单号
            List<string> list订单号 = dt.AsEnumerable().Select(x => x.Field<string>("生产订单号")).Distinct().ToList();
          DataTable dt入库 =  获取入库记录(list订单号);
            DataTable dt入库明细 = dt入库.Clone();

            //筛选出dt入库中的生产订单号 订单行号 dt中一致的
            foreach (DataRow dr1 in dt.Rows)
            {
                string 生产订单号 = dr1["生产订单号"].ToString();
                string 生产订单行号 = dr1["生产订单行号"].ToString();
                DataRow[] drs = dt入库.Select($"生产订单号='{生产订单号}' and 生产订单行号='{生产订单行号}'");
                if (drs.Length > 0)
                {
                    //如果dt入库明细不包含相同的订单号 订单行号 就进去
                    if (dt入库明细.Select($"生产订单号='{生产订单号}' and 生产订单行号='{生产订单行号}'").Length == 0)
                    {
                        dt入库明细.Rows.Add(drs[0].ItemArray);
                    }
                }
                else
                {
                    if (dt入库明细.Select($"生产订单号='{生产订单号}' and 生产订单行号='{生产订单行号}'").Length == 0)
                    {
                        //如果dt入库中没有这个订单号 订单行号 就添加一行
                        DataRow dr2 = dt入库明细.NewRow();
                        dr2["生产订单号"] = 生产订单号;
                        dr2["生产订单行号"] = 生产订单行号;
                        dr2["物料编号"] = dr1["物料编号"];
                        dr2["物料名称"] = dr1["物料名称"];
                        dr2["物料规格"] = dr1["物料规格"];
                        dr2["加工顺序"] = "99";
                        dr2["工序名称"] = "U8入库";
                        dr2["生产订单数量"] = dr1["生产订单数量"];
                        dr2["报工数量"] = 0;
                        dr2["未报工数量"] = dr1["生产订单数量"];
                        dt入库明细.Rows.Add(dr2);
                    }

                }
            }
            //合并两个datatable
            //dt.Merge(dt入库明细);
            //根据订单号和订单行号 加工顺序排序
            DataView dv = dt.DefaultView;
            dv.Sort = "生产订单号,生产订单行号,加工顺序";
            dt = dv.ToTable();

            //在dt最后一行添加合计行
            DataRow dr = dt.NewRow();
            dr["序号"] = "汇总";
            dr["生产订单数量"] = dt.Compute("sum(生产订单数量)", "");
            dr["报工数量"] = dt.Compute("sum(报工数量)", "");
            dr["未报工数量"] = dt.Compute("sum(未报工数量)", "");
            dt.Rows.Add(dr);

            dataGridView1.Columns.Clear();
            dataGridView1.DataSource = dt;
            //设置工序为 99 的背景色为红色
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.Cells["加工顺序"].Value.ToString() == "99")
                {
                    row.DefaultCellStyle.BackColor = Color.Red;
                }
            }
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
        }

        private DataTable 获取入库记录(List<string> list订单号)
        {
            //list订单号 拼接成in
            string str订单号 = string.Join("','", list订单号);
            string sql = $@"select 
                         View_生产订单明细含已关闭.生产订单号 生产订单号,View_生产订单明细含已关闭.行号 生产订单行号,
                         View_生产订单明细含已关闭.物料编号 物料编号,View_生产订单明细含已关闭.物料名称 物料名称,
                         View_生产订单明细含已关闭.物料规格 物料规格,
                         '99' 加工顺序,'U8入库' 工序名称,CAST( View_生产订单明细含已关闭.数量 as int)  生产订单数量,
                          CAST( isnull(sum(rdrecords10.iQuantity ),0)as int) 报工数量,CAST( sum(View_生产订单明细含已关闭.数量)- isnull(sum(rdrecords10.iQuantity ),0)as int)  未报工数量

                        from View_生产订单明细含已关闭 
                        --join Z_派工 on View_生产订单明细含已关闭.生产订单号 = Z_派工.订单号 and View_生产订单明细含已关闭.行号 = Z_派工.订单行号 
                        join rdrecords10 on rdrecords10.cmocode = View_生产订单明细含已关闭.生产订单号
                        and rdrecords10.imoseq = View_生产订单明细含已关闭.行号
                        where 1=1
                        and View_生产订单明细含已关闭.生产订单号 in ('{str订单号}') 
                        group by View_生产订单明细含已关闭.生产订单号,View_生产订单明细含已关闭.行号,
                        View_生产订单明细含已关闭.物料编号,View_生产订单明细含已关闭.物料名称,
                        View_生产订单明细含已关闭.物料规格,View_生产订单明细含已关闭.数量";

            DataTable dt = SqlSugarHelp.scope.Ado.GetDataTable(sql);
            return dt;
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.Text == "汇总")
            {
                汇总查询();
            }
            else
            {
                明细查询();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //导出
            if (dataGridView1.Rows.Count == 0)
            {
                MessageBox.Show("没有数据可导出");
                return;
            }
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel文件(*.xlsx)|*.xlsx";
            saveFileDialog.FileName = $"订单报工数{DateTime.Now.ToString("yyMMddHHmmss")}";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                MiniExcel.SaveAs(saveFileDialog.FileName, dataGridView1.DataSource, overwriteFile: true);
                MessageBox.Show("导出成功");
            }
        }
    }
}
